package homework_jdbc_01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;


public class User {
    public int executeInsert(){
        int r=0;
        String driver="com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://localhost:3306/jdbc_demo?useUnicode=true&characterEncoding=utf8";
        String user="root";
        String password="";
        Connection conn=null;
        Statement stmt=null;
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url,user,password);
            String sql ="insert into User(name,pwd,email,birthday)"+
                    " VALUES('张三','888888','zhangsan@126.com','1986-10-11'),"+
                    " ('李四','999999','lisi@126.com','1988-10-23'),"+
                    " ('王五','777777','wangwu@126.com','1990-9-11')";
            stmt = conn.createStatement();
            r = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    stmt =null;
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    conn = null;
                }
            }
        }

        return r;
    }
    public int executeUpdate(){
        int r=0;
        String driver="com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://localhost:3306/jdbc_demo?useUnicode=true&characterEncoding=utf8";
        String user="root";
        String password="";
        Connection conn=null;
        Statement stmt=null;
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url,user,password);
            String sql ="update user set birthday= NOW() where name='张三'";
            stmt = conn.createStatement();
            r = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    stmt =null;
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    conn = null;
                }
            }
        }

        return r;
    }
    public int executeDelete(){
        int r=0;
        String driver="com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://localhost:3306/jdbc_demo?useUnicode=true&characterEncoding=utf8";
        String user="root";
        String password="";
        Connection conn=null;
        Statement stmt=null;
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url,user,password);
            String sql ="delete from User where name='李四'";
            stmt = conn.createStatement();
            r = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    stmt =null;
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    conn = null;
                }
            }
        }

        return r;
    }

}
